const db = require('../config/db');
const { successResponse, errorResponse } = require('../utils/responseHandler');
const { buildFilterClause } = require('../utils/queryHelpers');

// Helper to get text for target_type, status, priority
const getTextLookups = (message) => {
    const targetTypeMap = { 1: '所有用户', 2: '指定角色', 3: '指定用户' };
    // Status interpretation based on your API response example (derived)
    const statusMapDB = { 1: '草稿', 2: '已发布', 3: '未生效DB', 4: '已过期DB', 5: '已删除DB' }; // Raw DB status

    let apiStatusText = '';
    const now = Date.now();
    if (message.status === 1) apiStatusText = '草稿';
    else if (message.status === 2) { // Published
        if (now < message.effective_start_at) apiStatusText = '未生效';
        else if (now > message.effective_end_at) apiStatusText = '已过期';
        else apiStatusText = '生效中';
    } else if (message.status === 5) {
        apiStatusText = '已删除'; // Or you might not show deleted ones
    } else {
        apiStatusText = statusMapDB[message.status] || '未知状态';
    }

    const priorityMap = { 1: '紧急', 2: '高', 3: '中', 4: '低' };

    return {
        target_type_text: targetTypeMap[message.target_type] || '未知',
        status_text: apiStatusText,
        priority_text: priorityMap[message.priority] || '未知',
    };
};

// Helper function to get message data without sending a response
const getMessageData = async (id) => {
    const messageQuery = `
        SELECT m.*,
               (SELECT COUNT(mr.id) FROM message_read mr WHERE mr.message_id = m.id) as read_count
        FROM message m
        WHERE m.id = $1 AND m.status != 5; -- Exclude deleted
    `;
    const messageResult = await db.query(messageQuery, [id]);

    if (messageResult.rows.length === 0) {
        return null;
    }
    const message = messageResult.rows[0];

    const contentsQuery = `SELECT language, title, content, body FROM message_content WHERE message_id = $1`;
    const contentsResult = await db.query(contentsQuery, [id]);

    const roleIdsQuery = `SELECT role_id FROM message_target_role WHERE message_id = $1`;
    const roleIdsResult = await db.query(roleIdsQuery, [id]);

    const userIdsQuery = `SELECT user_id FROM message_target_user WHERE message_id = $1`;
    const userIdsResult = await db.query(userIdsQuery, [id]);

    return {
        id: message.id,
        title: message.title, // Main title
        target_type: message.target_type,
        role_ids: roleIdsResult.rows.map(r => r.role_id),
        user_ids: userIdsResult.rows.map(u => u.user_id),
        notification_type: message.notification_type,
        status: message.status, // DB status
        ...getTextLookups(message), // Derived status_text
        priority: message.priority,
        created_at: Number(message.created_at),
        published_at: message.published_at ? Number(message.published_at) : null,
        effective_start_at: Number(message.effective_start_at),
        effective_end_at: Number(message.effective_end_at),
        contents: contentsResult.rows.map(c => ({...c, body: c.body || ''})), // ensure body is string if null
        read_count: parseInt(message.read_count),
        total_user_count: 0, // Placeholder - Implement proper calculation
    };
};

exports.getMessages = async (req, res, next) => {
    const {
        page = 1,
        page_size = 10,
        title,
        target_type,
        status, // This status is the API filter status (1-草稿, 2-已发布, 3-未生效, 4-生效中, 5-已过期)
        priority,
        start_time,
        end_time
    } = req.query;

    const offset = (page - 1) * page_size;

    try {
        const filters = { title, target_type, status, priority, start_time, end_time };
        const { clause: filterClause, params: filterParams, nextParamIndex } = buildFilterClause(filters, 1);

        const countQuery = `SELECT COUNT(DISTINCT m.id) FROM message m ${filterClause}`;
        const totalResult = await db.query(countQuery, filterParams);
        const total = parseInt(totalResult.rows[0].count);

        // For read_count and total_user_count, more complex queries or pre-aggregation might be needed for performance.
        // Here's a simplified approach. total_user_count is particularly tricky.
        let messagesQuery = `
            SELECT
                m.id, m.title AS message_main_title, m.target_type, m.notification_type, m.status, m.priority,
                m.created_at, m.published_at, m.effective_start_at, m.effective_end_at,
                (SELECT COUNT(mr.id) FROM message_read mr WHERE mr.message_id = m.id) as read_count
            -- total_user_count is complex. For 'all users', it's total system users.
            -- For 'roles'/'users', it's count of targeted roles/users.
            -- This is a placeholder:
            -- ,CASE
            --    WHEN m.target_type = 1 THEN (SELECT COUNT(*) FROM users) -- Placeholder for actual user table
            --    WHEN m.target_type = 2 THEN (SELECT COUNT(DISTINCT ur.user_id) FROM user_roles ur JOIN message_target_role mtr ON ur.role_id = mtr.role_id WHERE mtr.message_id = m.id) -- Placeholder
            --    WHEN m.target_type = 3 THEN (SELECT COUNT(*) FROM message_target_user mtu WHERE mtu.message_id = m.id)
            --    ELSE 0
            --  END as total_user_count_calculated
            FROM message m
                ${filterClause}
            ORDER BY m.priority ASC, m.published_at DESC NULLS LAST, m.created_at DESC
                LIMIT $${nextParamIndex} OFFSET $${nextParamIndex + 1}
        `;
        const messagesResult = await db.query(messagesQuery, [...filterParams, page_size, offset]);

        const list = messagesResult.rows.map(msg => ({
            id: msg.id,
            title: msg.message_main_title, // Using main title for list
            target_type: msg.target_type,
            notification_type: msg.notification_type, // This is JSONB
            status: msg.status, // DB status
            priority: msg.priority,
            created_at: Number(msg.created_at),
            published_at: msg.published_at ? Number(msg.published_at) : null,
            effective_start_at: Number(msg.effective_start_at),
            effective_end_at: Number(msg.effective_end_at),
            read_count: parseInt(msg.read_count),
            total_user_count: 0, // Placeholder - Implement proper calculation
            ...getTextLookups(msg) // Add text representations
        }));
        successResponse(res, { total, page: parseInt(page), page_size: parseInt(page_size), list });
    } catch (err) {
        next(err);
    }
};

exports.createMessage = async (req, res, next) => {
    const {
        title, // This is the main title for the message, not language specific
        target_type,
        role_ids = [],
        user_ids = [],
        notification_type, // e.g. ["app_popup"]
        priority = 3,
        effective_start_at,
        effective_end_at,
        contents, // [{ language: "zh", title: "你好", content: "...", body: "<p>...</p>" }, ...]
        status = 1 // 1-草稿, 2-已发布
    } = req.body;

    const created_by = req.user.userId; // From authMiddleware
    const created_at = Date.now();
    const published_at = (status === 2) ? Date.now() : null;

    if (!title || !target_type || !notification_type || !effective_start_at || !effective_end_at || !contents || !contents.length) {
        return errorResponse(res, 'Missing required fields.', 400);
    }
    const client = await db.getClient();
    try {
        await client.query('BEGIN');

        const messageQuery = `
            INSERT INTO message (title, target_type, notification_type, status, priority, created_at, published_at, effective_start_at, effective_end_at, created_by)
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
                RETURNING id;
        `;
        const messageValues = [title, target_type, JSON.stringify(notification_type), status, priority, created_at, published_at, effective_start_at, effective_end_at, created_by];
        const messageResult = await client.query(messageQuery, messageValues);
        const messageId = messageResult.rows[0].id;

        for (const content of contents) {
            const contentQuery = `
                INSERT INTO message_content (message_id, language, title, content, body)
                VALUES ($1, $2, $3, $4, $5);
            `;
            // Ensure body is null if not provided, not empty string for TEXT type
            await client.query(contentQuery, [messageId, content.language, content.title, content.content, content.body || null]);
        }

        if (target_type === 2 && role_ids.length > 0) { // Target specific roles
            for (const role_id of role_ids) {
                await client.query('INSERT INTO message_target_role (message_id, role_id) VALUES ($1, $2)', [messageId, role_id]);
            }
        } else if (target_type === 3 && user_ids.length > 0) { // Target specific users
            for (const user_id of user_ids) {
                await client.query('INSERT INTO message_target_user (message_id, user_id) VALUES ($1, $2)', [messageId, user_id]);
            }
        }

        await client.query('COMMIT');
        successResponse(res, { id: messageId }, 'Message created successfully.', 201);
    } catch (err) {
        await client.query('ROLLBACK');
        next(err);
    } finally {
        client.release();
    }
};

exports.getMessageById = async (req, res, next) => {
    const { id } = req.params;
    try {
        const messageData = await getMessageData(id);

        if (!messageData) {
            return errorResponse(res, 'Message not found or has been deleted.', 404);
        }

        successResponse(res, messageData);
    } catch (err) {
        next(err);
    }
};

exports.updateMessage = async (req, res, next) => {
    const { id } = req.params;
    const {title,
         target_type, role_ids = [], user_ids = [], notification_type,
        priority, effective_start_at, effective_end_at, contents=[], status
    } = req.body;
    console.log(req.body)
    const updated_by = req.user.userId;
    const updated_at = Date.now();

    // TODO: More comprehensive validation
    if (!title || !target_type || !notification_type || !effective_start_at || !effective_end_at || !contents || !contents.length) {
        return errorResponse(res, 'Missing required fields for update.', 400);
    }

    const client = await db.getClient();
    try {
        await client.query('BEGIN');

        // Check if message exists and is not deleted
        const checkQuery = 'SELECT status FROM message WHERE id = $1';
        const checkResult = await client.query(checkQuery, [id]);
        if (checkResult.rows.length === 0) {
            await client.query('ROLLBACK');
            client.release();
            return errorResponse(res, 'Message not found.', 404);
        }
        if (checkResult.rows[0].status === 5) { // 5 is '已删除'
            await client.query('ROLLBACK');
            client.release();
            return errorResponse(res, 'Cannot update a deleted message.', 400);
        }
        // Optionally, prevent editing of 'published' messages that are active or past, only allow editing drafts or future messages.
        // const currentMessageStatus = checkResult.rows[0].status;
        // if (currentMessageStatus === 2 && Date.now() > checkResult.rows[0].effective_start_at) {
        //    return errorResponse(res, 'Cannot update an active or past published message. Consider creating a new one or unpublishing first.', 400);
        // }

        const messageQuery = `
            UPDATE message
            SET title = $1, target_type = $2, notification_type = $3, status = $4, priority = $5,
                effective_start_at = $6, effective_end_at = $7, updated_at = $8, updated_by = $9
                ${status === 2 && !checkResult.rows[0].published_at ? `, published_at = ${updated_at}` : ''}
            WHERE id = $10;
        `;
        await client.query(messageQuery, [
            title, target_type, JSON.stringify(notification_type), status, priority,
            effective_start_at, effective_end_at, updated_at, updated_by, id
        ]);

        // Update contents: Delete existing and re-insert. Simpler than diffing.
        await client.query('DELETE FROM message_content WHERE message_id = $1', [id]);
        for (const content of contents) {
            // TODO: Sanitize rich text content.body
            await client.query(
              'INSERT INTO message_content (message_id, language, title, content, body) VALUES ($1, $2, $3, $4, $5)',
              [id, content.language, content.title, content.content, content.body || null]
            );
        }

        // Update target roles/users: Delete existing and re-insert
        await client.query('DELETE FROM message_target_role WHERE message_id = $1', [id]);
        await client.query('DELETE FROM message_target_user WHERE message_id = $1', [id]);

        if (target_type === 2 && role_ids.length > 0) {
            for (const role_id of role_ids) {
                await client.query('INSERT INTO message_target_role (message_id, role_id) VALUES ($1, $2)', [id, role_id]);
            }
        } else if (target_type === 3 && user_ids.length > 0) {
            for (const user_id of user_ids) {
                await client.query('INSERT INTO message_target_user (message_id, user_id) VALUES ($1, $2)', [id, user_id]);
            }
        }

        await client.query('COMMIT');

        // 获取更新后的消息数据
        try {
            const messageData = await getMessageData(id);
            if (messageData) {
                successResponse(res, messageData, 'Message updated successfully.');
            } else {
                successResponse(res, {id}, 'Message updated successfully. Could not retrieve full details post-update.');
            }
        } catch (err) {
            // 如果获取更新后的数据失败，至少返回ID
            successResponse(res, {id}, 'Message updated successfully. Could not retrieve full details post-update.');
        }
    } catch (err) {
        await client.query('ROLLBACK');
        next(err);
    } finally {
        client.release();
    }
};

exports.deleteMessage = async (req, res, next) => {
    const { id } = req.params;
    const client = await db.getClient();
    try {
        await client.query('BEGIN');
        // Physical delete as per spec.
        // ON DELETE CASCADE will handle related tables (message_content, target_role, target_user, message_read)
        const result = await client.query('DELETE FROM message WHERE id = $1 RETURNING id', [id]);

        if (result.rowCount === 0) {
            await client.query('ROLLBACK');
            return errorResponse(res, 'Message not found or already deleted.', 404);
        }

        await client.query('COMMIT');
        successResponse(res, null, 'Message deleted successfully.');
    } catch (err) {
        await client.query('ROLLBACK');
        next(err);
    } finally {
        client.release();
    }
};

exports.publishMessage = async (req, res, next) => {
    const { id } = req.params;
    const updated_by = req.user.userId;
    const now = Date.now();

    try {
        // Check current status: only drafts (status 1) can be published
        const currentMessage = await db.query('SELECT status, published_at FROM message WHERE id = $1', [id]);
        if (currentMessage.rows.length === 0) {
            return errorResponse(res, 'Message not found.', 404);
        }
        if (currentMessage.rows[0].status !== 1) { // Not a draft
            return errorResponse(res, 'Only draft messages can be published. This message might be already published, active, or archived.', 400);
        }

        const result = await db.query(
          'UPDATE message SET status = 2, published_at = $1, updated_at = $1, updated_by = $2 WHERE id = $3 AND status = 1 RETURNING id',
          [now, updated_by, id]
        );
        if (result.rowCount === 0) {
            // This case might happen if it was updated by another process between the check and update
            return errorResponse(res, 'Message could not be published. It might have been modified or is no longer a draft.', 409); // 409 Conflict
        }
        successResponse(res, null, 'Message published successfully.');
    } catch (err) {
        next(err);
    }
};
